import mysql.connector
from docx import Document

def extract_questions_and_answers(file_path):
    document = Document(file_path)
    questions = []
    current_question = None

    for para in document.paragraphs:
        text = para.text.strip()

        # 检查是否是题目（以数字和点开头，如 "1.", "2."）
        if text and (text[0].isdigit() and text[1] == '.'):
            if current_question:
                # 添加前一个题目的内容
                questions.append(current_question)
            # 创建新的题目
            current_question = {'text': text, 'answers': []}
        elif text:
            # 如果是题目之后的内容，则作为答案
            if current_question:
                current_question['answers'].append(text)

    # 添加最后一个题目
    if current_question:
        questions.append(current_question)

    return questions

def insert_questions_and_answers(exam_name, questions):
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="09876",
        database="school",
        #charset='utf8mb4'  # 确保连接使用 utf8mb4 编码
    )
    cursor = conn.cursor()

    try:
        for question in questions:
            question_text = question['text']
            # 插入题目及其对应的答案
            for answer in question['answers']:
                sql = "INSERT INTO questions_and_answers (exam_name, question_text, answer_text) VALUES (%s, %s, %s)"
                values = (exam_name, question_text, answer)
                cursor.execute(sql, values)

        conn.commit()
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

# 使用示例
file_path = 'C:\\Users\\于洋雯\\Desktop\\习思想 190题.docx'
exam_name = 'Sample Exam'

# 提取题目和答案
questions = extract_questions_and_answers(file_path)

# 将题目和答案上传到数据库
insert_questions_and_answers(exam_name, questions)
